includes/DatabasePostgreSQL.php

   1 <?php
   2
   3 /**
   4  * DO NOT USE !!!  Unless you want to help developping it.
   5  *
   6  * This file is an attempt to port the mysql database layer to postgreSQL. The
   7  * only thing done so far is s/mysql/pg/ and dieing if function haven't been
   8  * ported.
   9  *
  10  * As said brion 07/06/2004 :
  11  * "table definitions need to be changed. fulltext index needs to work differently
  12  * things that use the last insert id need to be changed. Probably other things
  13  * need to be changed. various semantics may be different."
  14  *
  15  * Hashar
  16  *
  17  * @package MediaWiki
  18  */
  19
  20 /**
  21  * Depends on database
  22  */
  23 require_once( 'Database.php' );
  24
  25 /**
  26  *
  27  * @package MediaWiki
  28  */
  29 class DatabasePgsql extends Database {
  30         var $mInsertId = NULL;
  31         var $mLastResult = NULL;
  32
  33         function DatabasePgsql($server = false, $user = false, $password = false, $dbName = false,
  34                 $failFunction = false, $flags = 0, $tablePrefix = 'get from global' )
  35         {
  36                 Database::Database( $server, $user, $password, $dbName, $failFunction, $flags, $tablePrefix );
  37         }
  38
  39         /* static */ function newFromParams( $server = false, $user = false, $password = false, $dbName = false,
  40                 $failFunction = false, $flags = 0, $tablePrefix = 'get from global' )
  41         {
  42                 return new DatabasePgsql( $server, $user, $password, $dbName, $failFunction, $flags, $tablePrefix );
  43         }
  44
  45         /**
  46          * Usually aborts on failure
  47          * If the failFunction is set to a non-zero integer, returns success
  48          */
  49         function open( $server, $user, $password, $dbName ) {
  50                 # Test for PostgreSQL support, to avoid suppressed fatal error
  51                 if ( !function_exists( 'pg_connect' ) ) {
  52                         die( "PostgreSQL functions missing, have you compiled PHP with the --with-pgsql option?\n" );
  53                 }
  54
  55                 global $wgDBschema;
  56
  57                 $this->close();
  58                 $this->mServer = $server;
  59                 $this->mUser = $user;
  60                 $this->mPassword = $password;
  61                 $this->mDBname = $dbName;
  62                 $this->mSchemas = array($wgDBschema,'public');
  63
  64                 $success = false;
  65
  66                 if ( '' != $dbName ) {
  67                         # start a database connection
  68                         $hstring="";
  69                         if ($server!=false && $server!="") {
  70                                 $hstring="host=$server ";
  71                         }
  72                         @$this->mConn = pg_connect("$hstring dbname=$dbName user=$user password=$password");
  73                         if ( $this->mConn == false ) {
  74                                 wfDebug( "DB connection error\n" );
  75                                 wfDebug( "Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n" );
  76                                 wfDebug( $this->lastError()."\n" );
  77                         } else {
  78                                 $this->setSchema();
  79                                 $this->mOpened = true;
  80                         }
  81                 }
  82                 return $this->mConn;
  83         }
  84
  85         /**
  86          * Closes a database connection, if it is open
  87          * Returns success, true if already closed
  88          */
  89         function close() {
  90                 $this->mOpened = false;
  91                 if ( $this->mConn ) {
  92                         return pg_close( $this->mConn );
  93                 } else {
  94                         return true;
  95                 }
  96         }
  97
  98         function doQuery( $sql ) {
  99                 return $this->mLastResult=pg_query( $this->mConn , $sql);
 100         }
 101
 102         function queryIgnore( $sql, $fname = '' ) {
 103                 return $this->query( $sql, $fname, true );
 104         }
 105
 106         function freeResult( $res ) {
 107                 if ( !@pg_free_result( $res ) ) {
 108                         wfDebugDieBacktrace( "Unable to free PostgreSQL result\n" );
 109                 }
 110         }
 111
 112         function fetchObject( $res ) {
 113                 @$row = pg_fetch_object( $res );
 114                 # FIXME: HACK HACK HACK HACK debug
 115
 116                 # TODO:
 117                 # hashar : not sure if the following test really trigger if the object
 118                 #          fetching failled.
 119                 if( pg_last_error($this->mConn) ) {
 120                         wfDebugDieBacktrace( 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) );
 121                 }
 122                 return $row;
 123         }
 124
 125         function fetchRow( $res ) {
 126                 @$row = pg_fetch_array( $res );
 127                 if( pg_last_error($this->mConn) ) {
 128                         wfDebugDieBacktrace( 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) );
 129                 }
 130                 return $row;
 131         }
 132
 133         function numRows( $res ) {
 134                 @$n = pg_num_rows( $res );
 135                 if( pg_last_error($this->mConn) ) {
 136                         wfDebugDieBacktrace( 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) );
 137                 }
 138                 return $n;
 139         }
 140         function numFields( $res ) { return pg_num_fields( $res ); }
 141         function fieldName( $res, $n ) { return pg_field_name( $res, $n ); }
 142
 143         /**
 144          * This must be called after nextSequenceVal
 145          */
 146         function insertId() {
 147                 return $this->mInsertId;
 148         }
 149
 150         function dataSeek( $res, $row ) { return pg_result_seek( $res, $row ); }
 151         function lastError() { return pg_last_error(); }
 152         function lastErrno() { return 1; }
 153
 154         function affectedRows() {
 155                 return pg_affected_rows( $this->mLastResult );
 156         }
 157
 158         /**
 159          * Returns information about an index
 160          * If errors are explicitly ignored, returns NULL on failure
 161          */
 162         function indexInfo( $table, $index, $fname = 'Database::indexExists' ) {
 163                 $sql = "SELECT indexname FROM pg_indexes WHERE tablename='$table'";
 164                 $res = $this->query( $sql, $fname );
 165                 if ( !$res ) {
 166                         return NULL;
 167                 }
 168
 169                 while ( $row = $this->fetchObject( $res ) ) {
 170                         if ( $row->Key_name == $index ) {
 171                                 return $row;
 172                         }
 173                 }
 174                 return false;
 175         }
 176
 177         function indexUnique ($table, $index, $fname = 'Database::indexUnique' ) {
 178                 $sql = "SELECT indexname FROM pg_indexes WHERE tablename='{$table}'".
 179                         " AND indexdef LIKE 'CREATE UNIQUE%({$index})'";
 180                 $res = $this->query( $sql, $fname );
 181                 if ( !$res )
 182                         return NULL;
 183                 while ($row = $this->fetchObject( $res ))
 184                         return true;
 185                 return false;
 186
 187         }
 188
 189         function fieldInfo( $table, $field ) {
 190                 wfDebugDieBacktrace( 'Database::fieldInfo() error : mysql_fetch_field() not implemented for postgre' );
 191                 /*
 192                 $res = $this->query( "SELECT * FROM '$table' LIMIT 1" );
 193                 $n = pg_num_fields( $res );
 194                 for( $i = 0; $i < $n; $i++ ) {
 195                         // FIXME
 196                         wfDebugDieBacktrace( "Database::fieldInfo() error : mysql_fetch_field() not implemented for postgre" );
 197                         $meta = mysql_fetch_field( $res, $i );
 198                         if( $field == $meta->name ) {
 199                                 return $meta;
 200                         }
 201                 }
 202                 return false;*/
 203         }
 204
 205         function insert( $table, $a, $fname = 'Database::insert', $options = array() ) {
 206                 # PostgreSQL doesn't support options
 207                 # We have a go at faking one of them
 208                 # TODO: DELAYED, LOW_PRIORITY
 209
 210                 if ( !is_array($options))
 211                         $options = array($options);
 212
 213                 if ( in_array( 'IGNORE', $options ) )
 214                         $oldIgnore = $this->ignoreErrors( true );
 215
 216                 # IGNORE is performed using single-row inserts, ignoring errors in each
 217                 # FIXME: need some way to distiguish between key collision and other types of error
 218                 $oldIgnore = $this->ignoreErrors( true );
 219                 if ( !is_array( reset( $a ) ) ) {
 220                         $a = array( $a );
 221                 }
 222                 foreach ( $a as $row ) {
 223                         parent::insert( $table, $row, $fname, array() );
 224                 }
 225                 $this->ignoreErrors( $oldIgnore );
 226                 $retVal = true;
 227
 228                 if ( in_array( 'IGNORE', $options ) )
 229                         $this->ignoreErrors( $oldIgnore );
 230
 231                 return $retVal;
 232         }
 233
 234         function startTimer( $timeout )
 235         {
 236                 global $IP;
 237                 wfDebugDieBacktrace( 'Database::startTimer() error : mysql_thread_id() not implemented for postgre' );
 238                 /*$tid = mysql_thread_id( $this->mConn );
 239                 exec( "php $IP/killthread.php $timeout $tid &>/dev/null &" );*/
 240         }
 241
 242         function tableName( $name ) {
 243                 # First run any transformations from the parent object
 244                 $name = parent::tableName( $name );
 245
 246                 # Replace backticks into double quotes
 247                 $name = strtr($name,'`','"');
 248
 249                 # Now quote PG reserved keywords
 250                 switch( $name ) {
 251                         case 'user':
 252                         case 'old':
 253                         case 'group':
 254                                 return '"' . $name . '"';
 255
 256                         default:
 257                                 return $name;
 258                 }
 259         }
 260
 261         function strencode( $s ) {
 262                 return addslashes( $s );
 263         }
 264
 265         /**
 266          * Return the next in a sequence, save the value for retrieval via insertId()
 267          */
 268         function nextSequenceValue( $seqName ) {
 269                 $value = $this->selectField(''," nextval('" . $seqName . "')");
 270                 $this->mInsertId = $value;
 271                 return $value;
 272         }
 273
 274         /**
 275          * USE INDEX clause
 276          * PostgreSQL doesn't have them and returns ""
 277          */
 278         function useIndexClause( $index ) {
 279                 return '';
 280         }
 281
 282         # REPLACE query wrapper
 283         # PostgreSQL simulates this with a DELETE followed by INSERT
 284         # $row is the row to insert, an associative array
 285         # $uniqueIndexes is an array of indexes. Each element may be either a
 286         # field name or an array of field names
 287         #
 288         # It may be more efficient to leave off unique indexes which are unlikely to collide.
 289         # However if you do this, you run the risk of encountering errors which wouldn't have
 290         # occurred in MySQL
 291         function replace( $table, $uniqueIndexes, $rows, $fname = 'Database::replace' ) {
 292                 $table = $this->tableName( $table );
 293
 294                 if (count($rows)==0) {
 295                         return;
 296                 }
 297
 298                 # Single row case
 299                 if ( !is_array( reset( $rows ) ) ) {
 300                         $rows = array( $rows );
 301                 }
 302
 303                 foreach( $rows as $row ) {
 304                         # Delete rows which collide
 305                         if ( $uniqueIndexes ) {
 306                                 $sql = "DELETE FROM $table WHERE ";
 307                                 $first = true;
 308                                 foreach ( $uniqueIndexes as $index ) {
 309                                         if ( $first ) {
 310                                                 $first = false;
 311                                                 $sql .= "(";
 312                                         } else {
 313                                                 $sql .= ') OR (';
 314                                         }
 315                                         if ( is_array( $index ) ) {
 316                                                 $first2 = true;
 317                                                 foreach ( $index as $col ) {
 318                                                         if ( $first2 ) {
 319                                                                 $first2 = false;
 320                                                         } else {
 321                                                                 $sql .= ' AND ';
 322                                                         }
 323                                                         $sql .= $col.'=' . $this->addQuotes( $row[$col] );
 324                                                 }
 325                                         } else {
 326                                                 $sql .= $index.'=' . $this->addQuotes( $row[$index] );
 327                                         }
 328                                 }
 329                                 $sql .= ')';
 330                                 $this->query( $sql, $fname );
 331                         }
 332
 333                         # Now insert the row
 334                         $sql = "INSERT INTO $table (" . $this->makeList( array_keys( $row ), LIST_NAMES ) .') VALUES (' .
 335                                 $this->makeList( $row, LIST_COMMA ) . ')';
 336                         $this->query( $sql, $fname );
 337                 }
 338         }
 339
 340         # DELETE where the condition is a join
 341         function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = "Database::deleteJoin" ) {
 342                 if ( !$conds ) {
 343                         wfDebugDieBacktrace( 'Database::deleteJoin() called with empty $conds' );
 344                 }
 345
 346                 $delTable = $this->tableName( $delTable );
 347                 $joinTable = $this->tableName( $joinTable );
 348                 $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
 349                 if ( $conds != '*' ) {
 350                         $sql .= 'WHERE ' . $this->makeList( $conds, LIST_AND );
 351                 }
 352                 $sql .= ')';
 353
 354                 $this->query( $sql, $fname );
 355         }
 356
 357         # Returns the size of a text field, or -1 for "unlimited"
 358         function textFieldSize( $table, $field ) {
 359                 $table = $this->tableName( $table );
 360                 $sql = "SELECT t.typname as ftype,a.atttypmod as size
 361                         FROM pg_class c, pg_attribute a, pg_type t
 362                         WHERE relname='$table' AND a.attrelid=c.oid AND
 363                                 a.atttypid=t.oid and a.attname='$field'";
 364                 $res =$this->query($sql);
 365                 $row=$this->fetchObject($res);
 366                 if ($row->ftype=="varchar") {
 367                         $size=$row->size-4;
 368                 } else {
 369                         $size=$row->size;
 370                 }
 371                 $this->freeResult( $res );
 372                 return $size;
 373         }
 374
 375         function lowPriorityOption() {
 376                 return '';
 377         }
 378
 379         function limitResult($limit,$offset) {
 380                 return " LIMIT $limit ".(is_numeric($offset)?" OFFSET {$offset} ":"");
 381         }
 382
 383         /**
 384          * Returns an SQL expression for a simple conditional.
 385          * Uses CASE on PostgreSQL.
 386          *
 387          * @param string $cond SQL expression which will result in a boolean value
 388          * @param string $trueVal SQL expression to return if true
 389          * @param string $falseVal SQL expression to return if false
 390          * @return string SQL fragment
 391          */
 392         function conditional( $cond, $trueVal, $falseVal ) {
 393                 return " (CASE WHEN $cond THEN $trueVal ELSE $falseVal END) ";
 394         }
 395
 396         # FIXME: actually detecting deadlocks might be nice
 397         function wasDeadlock() {
 398                 return false;
 399         }
 400
 401         # Return DB-style timestamp used for MySQL schema
 402         function timestamp( $ts=0 ) {
 403                 return wfTimestamp(TS_DB,$ts);
 404         }
 405
 406         /**
 407          * Return aggregated value function call
 408          */
 409         function aggregateValue ($valuedata,$valuename='value') {
 410                 return $valuedata;
 411         }
 412
 413
 414         function reportQueryError( $error, $errno, $sql, $fname, $tempIgnore = false ) {
 415                 $message = "A database error has occurred\n" .
 416                         "Query: $sql\n" .
 417                         "Function: $fname\n" .
 418                         "Error: $errno $error\n";
 419                 wfDebugDieBacktrace($message);
 420         }
 421
 422         /**
 423          * @return string wikitext of a link to the server software's web site
 424          */
 425         function getSoftwareLink() {
 426                 return "[http://www.postgresql.org/ PostgreSQL]";
 427         }
 428
 429         /**
 430          * @return string Version information from the database
 431          */
 432         function getServerVersion() {
 433                 $res = $this->query( "SELECT version()" );
 434                 $row = $this->fetchRow( $res );
 435                 $version = $row[0];
 436                 $this->freeResult( $res );
 437                 return $version;
 438         }
 439
 440         function setSchema($schema=false) {
 441                 $schemas=$this->mSchemas;
 442                 if ($schema) { array_unshift($schemas,$schema); }
 443                 $searchpath=$this->makeList($schemas,LIST_NAMES);
 444                 $this->query("SET search_path = $searchpath");
 445         }
 446 }
 447
 448 /**
 449  * Just an alias.
 450  * @package MediaWiki
 451  */
 452 class DatabasePostgreSQL extends DatabasePgsql {
 453 }
 454
 455 ?>